﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.Data.SqlClient;
using System.Configuration;

[DataContract]
class StopMarker_Stop
{
    [DataMember(Order = 0)]
    public string id { get; set; }

    [DataMember(Order = 1)]
    public string city { get; set; }

    [DataMember(Order = 2)]
    public string goback { get; set; }

    [DataMember(Order = 3)]
    public string route_id { get; set; }

    [DataMember(Order = 4)]
    public string name { get; set; }

    [DataMember(Order = 5)]
    public float lat { get; set; }

    [DataMember(Order = 6)]
    public float lng { get; set; }

    [DataMember(Order = 7)]
    public IList<StopMarker_Marker> marker { get; set; }
}

[DataContract]
class StopMarker_PStop
{
    [DataMember(Order = 0)]
    public string id { get; set; }

    [DataMember(Order = 1)]
    public string city { get; set; }

    [DataMember(Order = 2)]
    public string goback { get; set; }

    [DataMember(Order = 3)]
    public string route_id { get; set; }

    [DataMember(Order = 4)]
    public string name { get; set; }

    [DataMember(Order = 5)]
    public float lat { get; set; }

    [DataMember(Order = 6)]
    public float lng { get; set; }

    [DataMember(Order = 7)]
    public IList<StopMarker_PMarker> marker { get; set; }
}

[DataContract]
class StopMarker_Marker
{
    [DataMember(Order = 0)]
    public string id { get; set; }

    [DataMember(Order = 1)]
    public string name { get; set; }

    [DataMember(Order = 2)]
    public string class_id { get; set; }

    [DataMember(Order = 3)]
    public string recommend { get; set; }

    [DataMember(Order = 4)]
    public string starPoint { get; set; }

    [DataMember(Order = 5)]
    public string dataSource { get; set; }

    [DataMember(Order = 6)]
    public string class_name { get; set; }

}

[DataContract]
class StopMarker_PMarker
{
    [DataMember(Order = 0)]
    public string id { get; set; }

    [DataMember(Order = 1)]
    public string name { get; set; }

    [DataMember(Order = 2)]
    public string city { get; set; }

    [DataMember(Order = 3)]
    public string area { get; set; }

    [DataMember(Order = 4)]
    public string address { get; set; }

    [DataMember(Order = 5)]
    public string phone { get; set; }

    [DataMember(Order = 6)]
    public string desc { get; set; }

    [DataMember(Order = 7)]
    public float lat { get; set; }

    [DataMember(Order = 8)]
    public float lng { get; set; }

    [DataMember(Order = 9)]
    public string class_id { get; set; }

    [DataMember(Order = 10)]
    public IList<StopMarker_photo> photo { get; set; }

    [DataMember(Order = 11)]
    public string recommend { get; set; }

    [DataMember(Order = 12)]
    public string starPoint { get; set; }

    [DataMember(Order = 13)]
    public string dataSource { get; set; }

    [DataMember(Order = 14)]
    public string class_name { get; set; }

}

[DataContract]
class StopMarker_photo
{
    [DataMember(Order = 0)]
    public string url { get; set; }

    [DataMember(Order = 1)]
    public int sort { get; set; }
}

[DataContract]
class StopMarker_result
{
    [DataMember(Order = 0)]
    public IList<StopMarker_Stop> stop { get; set; }
}

[DataContract]
class StopMarker_Presult
{
    [DataMember(Order = 0)]
    public IList<StopMarker_PStop> stop { get; set; }
}

public partial class apinew_StopMarker : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //判別是否有站牌ID 及 城市資訊
        if (!string.IsNullOrEmpty(Request.QueryString["stid"]) && !string.IsNullOrEmpty(Request.QueryString["city"]))
        {
            if (!string.IsNullOrEmpty(Request.QueryString["info"]))
            {
                if (Request.QueryString["info"].Contains("marker.photo"))
                {
                    infoWithPhoto();
                }
            }
            NormalInfo();
        }
    }

    /// <summary>
    /// 拿取帶有照片的資訊
    /// </summary>
    public void infoWithPhoto() {
        using (MyDb.Db db = new MyDb.Db())
        {
            int MarkerShowNumber = db.MarkerShowNumber(ConfigurationManager.AppSettings["MarkerShowNumber"]);

            StopMarker_Presult result = new StopMarker_Presult();

            string gobSQL = "", stid = Request.QueryString["stid"], city = Request.QueryString["city"];
            result.stop = new List<StopMarker_PStop>();

            if (!string.IsNullOrEmpty(Request.QueryString["gob"]))
            {
                gobSQL = string.Format(" and a.goback={0}", Request.QueryString["gob"]);
            }

            //先使用stid+city查詢站牌基本資訊
            using (DataTable StopDT = db.getDataTable(string.Format("select Id,city,goBack,routeId,nameZh,latitude,longitude from stop where id='{0}' and city='{1}' {2}", stid, city, gobSQL), "StopTable"))
            { 
                //若有去回程 則會有兩筆
                foreach (DataRow SRow in StopDT.Rows)
                {
                    StopMarker_PStop Stop = new StopMarker_PStop();
                    Stop.id = SRow["Id"].ToString().Trim();
                    Stop.city = SRow["city"].ToString().Trim();
                    Stop.goback = SRow["goback"].ToString().Trim();
                    Stop.route_id = SRow["routeID"].ToString().Trim();
                    Stop.name = SRow["nameZh"].ToString().Trim();
                    Stop.lat = float.Parse(SRow["latitude"].ToString().Trim());
                    Stop.lng = float.Parse(SRow["longitude"].ToString().Trim());
                    Stop.marker = new List<StopMarker_PMarker>();


                    //搜尋站牌上所有marker 帶有照片屬性
                    using (DataTable MarkerTable = db.getDataTable(string.Format("select  a.id,b.ID as MarkerID,b.NameZh,b.MarkerClassID,b.Recommend,b.StarPoint,b.DataSource,c.NameZh as CName,a.ShowCount,b.ShowPriority,d.URL,d.Sort,a.City,b.Area,b.Address,b.Phone,b.[desc] as descript,b.Lat,b.Lng from stopmarker_cache a left join markerInfo b on a.MarkerID=b.ID left join MarkerClass c on b.MarkerClassID=c.ID left join MarkerPhoto d on d.MarkerID=a.MarkerID  where a.StopID='{0}' and a.City='{1}' order by c.Orders,b.NameZh,d.Sort", stid, city), "MarkerTable"))
                    {
                        
                        //拿取分類
                        DataRow[] ClassRows = getClassRow(db, stid, city);
                        string updateString = "";
                        List<string> SelectID = new List<string>();
                        foreach (DataRow ClassRow in ClassRows)
                        {
                            //拿取所屬class符合條件之資料列
                            DataRow[] DetailRows = getDetailRows(MarkerTable, ClassRow["MarkerClassID"].ToString().Trim(), MarkerShowNumber, "Photo");
                            foreach (DataRow DetailRow in DetailRows)
                            {
                                DataRow[] Rows = MarkerTable.Select(string.Format("MarkerID='{0}'",DetailRow["MarkerID"]));
                                Stop.marker.Add(generatorPMarker(Rows));
                                SelectID.Add(DetailRow["id"].ToString());
                            }
                            updateString = generateUpdCmd(stid, city, SelectID.ToArray());
                            db.getScalar(updateString);
                        }
                        result.stop.Add(Stop);
                    }
                    StopDT.Clear();
                    Response.Write(MyDb.JSON.stringify(result));
                    result = null;
                    Response.End();
                }
            }
        }
    }

    /// <summary>
    /// 拿取一般資訊(不帶照片)
    /// </summary>
    public void NormalInfo() {
        using (MyDb.Db db = new MyDb.Db())
        {
            int MarkerShowNumber = db.MarkerShowNumber(ConfigurationManager.AppSettings["MarkerShowNumber"]);

            StopMarker_result result = new StopMarker_result();
            string gobSQL="",stid=Request.QueryString["stid"],city=Request.QueryString["city"];
            result.stop = new List<StopMarker_Stop>();

            if (!string.IsNullOrEmpty(Request.QueryString["gob"])) {
                gobSQL = string.Format(" and a.goback={0}", Request.QueryString["gob"]);
            }


            //先使用stid+city查詢站牌基本資訊
            using (DataTable StopDT = db.getDataTable(string.Format("select Id,city,goBack,routeId,nameZh,latitude,longitude from stop where id='{0}' and city='{1}' {2}",stid,city,gobSQL), "StopTable"))
            {
                //若有去回程 則會有兩筆
                foreach (DataRow SRow in StopDT.Rows) {
                    StopMarker_Stop Stop = new StopMarker_Stop();
                    Stop.id = SRow["Id"].ToString().Trim();
                    Stop.city = SRow["city"].ToString().Trim();
                    Stop.goback = SRow["goback"].ToString().Trim();
                    Stop.route_id = SRow["routeID"].ToString().Trim();
                    Stop.name = SRow["nameZh"].ToString().Trim();
                    Stop.lat = float.Parse(SRow["latitude"].ToString().Trim());
                    Stop.lng = float.Parse(SRow["longitude"].ToString().Trim());
                    Stop.marker = new List<StopMarker_Marker>();

                    //搜尋站牌上所有marker
                    using (DataTable MarkerTable = db.getDataTable(string.Format("select  a.id,b.ID as MarkerID,b.NameZh,b.MarkerClassID,b.Recommend,b.StarPoint,b.DataSource,c.NameZh as CName,a.ShowCount,b.ShowPriority from stopmarker_cache a left join markerInfo b on a.MarkerID=b.ID left join MarkerClass c on b.MarkerClassID=c.ID where a.StopID='{0}' and a.City='{1}' order by c.Orders", stid, city), "MarkerTable"))
                    { 

                        //拿取分類
                        DataRow[] ClassRows = getClassRow(db,stid,city);
                        string updateString = "";
                        List<string> SelectID = new List<string>();
                        foreach (DataRow ClassRow in ClassRows) { 
                            //拿取所屬class符合條件之資料列
                            DataRow[] DetailRows = getDetailRows(MarkerTable, ClassRow["MarkerClassID"].ToString().Trim(), MarkerShowNumber, "Normal");
                            foreach (DataRow DetailRow in DetailRows) {
                                Stop.marker.Add(generatorMarker(DetailRow));
                                SelectID.Add(DetailRow["id"].ToString());
                            }
                        }
                        updateString = generateUpdCmd(stid,city,SelectID.ToArray());
                        db.getScalar(updateString);
                    }
                    result.stop.Add(Stop);
                }
                StopDT.Clear();
                Response.Write(MyDb.JSON.stringify(result));
                result = null;
                Response.End();
            }

        }        
    }

    /// <summary>
    /// 拿取此站牌上的marker分類
    /// </summary>
    /// <param name="db"></param>
    /// <param name="stid"></param>
    /// <param name="city"></param>
    /// <returns></returns>
    public DataRow[] getClassRow(MyDb.Db db,string stid, string city)
    {
        DataTable ClassTable = db.getDataTable(string.Format("select b.MarkerClassID from stopmarker_cache a left join markerInfo b on a.MarkerID=b.ID left join MarkerClass c on b.MarkerClassID=c.ID where a.StopID='{0}' and a.City='{1}' group by MarkerClassID,c.Orders order by c.Orders", stid, city), "ClassTable");

        DataRow[] ClassRows = ClassTable.Select();
        return ClassRows;
    }

    /// <summary>
    /// 拿取符合資料列
    /// </summary>
    /// <param name="MarkerTable"></param>
    /// <param name="ClassID"></param>
    /// <param name="MarkerShowNumber"></param>
    /// <param name="type"></param>
    /// <returns></returns>
    public DataRow[] getDetailRows(DataTable MarkerTable, string ClassID, int MarkerShowNumber , string type) {
        List<DataRow> ResultRows = new List<DataRow>();

        //選取DataSourc='shop'的資料列
        DataRow[] ShopDetailRows = MarkerTable.Select(string.Format("MarkerClassID='{0}' and DataSource='shop'",ClassID));

        //shop大於所需數量
        if (ShopDetailRows.Length > MarkerShowNumber)
        {
            ResultRows = RandomRow(ShopDetailRows, MarkerShowNumber);
            return ResultRows.ToArray();
        }
        else {
            ResultRows = RandomRow(ShopDetailRows, ShopDetailRows.Length);
        }

        //shop小於所需數量
        MarkerShowNumber = MarkerShowNumber - ResultRows.Count;
        DataRow[] DetailRows = MarkerTable.Select(string.Format("MarkerClassID='{0}' and ShowPriority>=ShowCount and DataSource<>'shop'",ClassID));
        if (DetailRows.Length >= MarkerShowNumber)
        {
            foreach (DataRow Detail in RandomRow(DetailRows, MarkerShowNumber)) {
                ResultRows.Add(Detail);
            }
            return ResultRows.ToArray();
        }
        else {
            foreach (DataRow Detail in RandomRow(DetailRows, DetailRows.Length))
            {
                ResultRows.Add(Detail);
                MarkerShowNumber--;
            }
            DataRow[] ExDetailRows = MarkerTable.Select(string.Format("MarkerClassID='{0}' and ShowPriority<ShowCount and DataSource<>'shop'", ClassID));

            foreach (DataRow Detail in RandomRow(ExDetailRows, MarkerShowNumber)) {
                ResultRows.Add(Detail);
            }

            return ResultRows.ToArray();
        }
    }

    /// <summary>
    /// 隨機選取資料列
    /// </summary>
    /// <param name="Rows"></param>
    /// <param name="MarkerShowNumber"></param>
    /// <returns></returns>
    public List<DataRow> RandomRow(DataRow[] Rows,int MarkerShowNumber) {
        List<DataRow> DetailList = Rows.ToList<DataRow>();
        List<DataRow> SelectRows = new List<DataRow>();
        if (Rows.Length < MarkerShowNumber) { MarkerShowNumber = Rows.Length; }
        for (int i = 0; i < MarkerShowNumber; i++)
        {
            int random = GetRandomNumber(DetailList.Count);
            SelectRows.Add(DetailList[random]);
            DetailList.RemoveAll(delegate(DataRow DR) { return DR["MarkerID"].ToString() == DetailList[random]["MarkerID"].ToString(); });
            if (DetailList.Count == 0) { break; }
        }
        return SelectRows;
    }


    /// <summary>
    /// 取得隨機數
    /// </summary>
    /// <param name="max"></param>
    /// <returns></returns>
    public int GetRandomNumber(int max)
    {
        Random ran = new Random();
        int number = ran.Next(0, max);
        return number;
    }

    /// <summary>
    /// 生成更新指令
    /// </summary>
    /// <param name="item"></param>
    /// <returns></returns>
    /// 
    public string generateUpdCmd(string stid, string city, string[] SelectID)
    {
        string updateString = string.Format("update stopmarker_cache  set ShowCount=0 where ShowCount>(select ShowPriority from markerInfo where markerInfo.ID=stopmarker_cache.MarkerID) and StopID='{0}' and City='{1}';",stid,city);
        string AddString = "update stopmarker_cache set ShowCount = ShowCount+1 where ";
        for (int i = 0; i < SelectID.Length; i++) {
            if (i != 0) {
                AddString += " or ";
            }
            AddString += string.Format("id={0}", SelectID[i]);
        }

        return updateString + AddString;
    } 

    private StopMarker_Marker generatorMarker(DataRow item)
    {
        StopMarker_Marker marker = new StopMarker_Marker();
        marker.id = item["MarkerID"].ToString().Trim();
        marker.name = item["NameZh"].ToString().TrimEnd();
        marker.class_id = item["MarkerClassID"].ToString().Trim();
        marker.recommend = item["Recommend"].ToString().Trim();
        marker.starPoint = item["starpoint"].ToString().Trim();
        marker.dataSource = item["datasource"].ToString().Trim();
        marker.class_name = item["CName"].ToString().Trim();
        return marker;
    }

    private StopMarker_PMarker generatorPMarker(DataRow[] items)
    {
        StopMarker_PMarker marker = new StopMarker_PMarker();
        marker.id = items[0]["MarkerID"].ToString().Trim();
        marker.name = items[0]["NameZh"].ToString().TrimEnd();
        marker.city = items[0]["city"].ToString().Trim();
        marker.area = items[0]["area"].ToString().Trim();
        marker.address = items[0]["address"].ToString().Trim();
        marker.phone = items[0]["phone"].ToString().Trim();
        marker.desc = items[0]["descript"].ToString().Trim();
        marker.id = items[0]["MarkerID"].ToString().Trim();
        marker.name = items[0]["NameZh"].ToString().TrimEnd();
        marker.class_id = items[0]["MarkerClassID"].ToString().Trim();
        marker.recommend = items[0]["Recommend"].ToString().Trim();
        marker.starPoint = items[0]["starpoint"].ToString().Trim();
        marker.dataSource = items[0]["datasource"].ToString().Trim();
        marker.lat = float.Parse(items[0]["lat"].ToString().Trim());
        marker.lng = float.Parse(items[0]["lng"].ToString().Trim());
        marker.class_name = items[0]["CName"].ToString().Trim();
        marker.photo = new List<StopMarker_photo>();
        foreach (DataRow item in items)
        {
            StopMarker_photo pitem = new StopMarker_photo();
            pitem.url = item["url"].ToString();
            pitem.sort = Convert.ToInt16(item["Sort"].ToString());
            marker.photo.Add(pitem);
        }
        return marker;
    }
}